﻿--Các store procedure tùy biến theo chức năng
--Tạo ngày 6/4/2012
--Người tạo: liemqv@gmail.com
--Ngày cập nhật: 14/04/2012
--Người sửa: liemqv
--
USE hou_elearning
GO
--*****************************************/
--sp kiểm tra đăng nhập
--*****************************************/
if exists (select * from sysobjects where id = object_id(N'[sp_tblNguoidung_Login]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [sp_tblNguoidung_Login]
GO

/* Procedure sp_tblNguoidung_Login*/
CREATE PROCEDURE sp_tblNguoidung_Login
@sTendangnhap Varchar(30),
@sMatkhau Varchar(50)
AS
SELECT
	--[PK_iBaihocID],
	--[sTenbaihoc],
	--[sThongtinbaihoc],
	--[iTrangthai],
	--[FK_iTainguyenID]

*
FROM
	[tblNguoidung]
WHERE sTendangnhap = @sTendangnhap AND sMatkhau = @sMatkhau
GO
--*****************************************/
--End sp kiểm tra đăng nhập
--*****************************************/

--*****************************************/
--sp lấy quyền theo tên viết tắt
--*****************************************/
CREATE proc spQuyen_GetByShortName
@sTenviettat Varchar(10)
AS
BEGIN
	Select * FROM tblQuyen WHERE sTenviettat = @sTenviettat
END
GO

--*****************************************/
--sp lấy quyền theo tên
--*****************************************/
CREATE proc spQuyen_GetByName
@sTenquyen NVarchar(100)
AS
BEGIN
	Select * FROM tblQuyen WHERE sTenquyen = @sTenquyen
END
GO

--*****************************************/
--sp lấy nhóm người dùng theo tên
--*****************************************/
CREATE proc spNhomnguoidung_GetByTennhom
@sTennhom NVarchar(100)
AS
BEGIN
	Select * FROM tblNhomnguoidung WHERE sTennhom = @sTennhom
END
GO


--*****************************************/
--Cập nhật các sp của quyền, thay Char thành Varchar
--*****************************************/
ALTER PROC [dbo].[spQuyen_Insert]
    
@PK_iQuyenID smallint Output,
@sTenquyen nvarchar(200),
@sTenviettat Varchar(10),
@sGhichu nvarchar(400)
AS
    INSERT INTO tblQuyen(sTenquyen,sTenviettat,sGhichu)
    VALUES(@sTenquyen,@sTenviettat,@sGhichu)
    SELECT @PK_iQuyenID=SCOPE_IDENTITY()
GO

ALTER PROC [dbo].[spQuyen_Update] 
    
@PK_iQuyenID smallint Output,
@sTenquyen nvarchar(200),
@sTenviettat Varchar(10),
@sGhichu nvarchar(400)
AS
UPDATE tblQuyen
SET
    sTenquyen=@sTenquyen,sTenviettat=@sTenviettat,sGhichu=@sGhichu
WHERE PK_iQuyenID=@PK_iQuyenID
GO


--*****************************************/
--End sp Quyền
--*****************************************/

--sp top khóa học nổi bật
--drop proc spKhoahoc_GetTop
CREATE view vTopKhoahoc
as
	select TOP 3 FK_iKhoahocID as PK_iKhoahocID, count(*) as count
	FROM tblDanhgia_Khoahoc
	GROUP BY FK_iKhoahocID
	order BY count DESC
go

CREATE proc spKhoahoc_GetTop
as
begin
	select top 3 *
	from tblKhoahoc LEFT JOIN vTopKhoahoc
	ON tblKhoahoc.PK_iKhoahocID = vTopKhoahoc.PK_iKhoahocID
	ORDER BY vTopKhoahoc.count DESC
end
GO
--EXEC spKhoahoc_GetTop
--EXEC spKhoahoc_GetTopOrderByTime
--sp top khóa học mới nhất
--DROP proc spKhoahoc_GetTopByTime
CREATE proc spKhoahoc_GetTopOrderByTime
as
begin
	select top 3 * from tblKhoahoc
	ORDER BY dNgaytao DESC
end
go
--EXEC spKhoahoc_GetTopOrderByTime
--sp top bài học nổi bật

CREATE view vTopBaihoc
as
	select TOP 3 FK_iBaihocID as PK_iBaihocID, count(*) as count
	FROM tblDanhgia_Baihoc
	GROUP BY FK_iBaihocID
	order BY count DESC
go
--drop proc spBaihoc_GetTop
CREATE proc spBaihoc_GetTop
as
begin
	select top 3 *
	from tblBaihoc LEFT JOIN vTopBaihoc
	ON tblBaihoc.PK_iBaihocID = vTopBaihoc.PK_iBaihocID
	ORDER BY vTopBaihoc.count DESC
end
go
--EXEC spBaihoc_GetTop


--sp top bài học mới nhất
CREATE proc spBaihoc_GetTopByTime
as
begin
	select top 5 * from tblBaihoc
	ORDER BY dNgaycapnhat DESC
end
GO
--sp top giảng viên tiêu biểu
CREATE view vTopGiangvien
as
	select TOP 12 FK_iNguoiduocdanhgiaID as PK_iNguoidungID, count(*) as count
	FROM tblDanhgia_Nguoidung
	GROUP BY FK_iNguoiduocdanhgiaID
	order BY count DESC
go

CREATE proc spGiangvien_GetTopByVote
as
begin
	select top 12 *
	from tblNguoidung LEFT JOIN vTopGiangvien
	ON tblNguoidung.PK_iNguoidungID = vTopGiangvien.PK_iNguoidungID
	ORDER BY vTopGiangvien.count DESC
end
GO
--sp get Khoahoc_Hocvien by FK_iKhoahocID
CREATE PROC [dbo].[spKhoahoc_Hocvien_GetByFK_iKhoahocID]
    @FK_iKhoahocID bigint
AS
    SELECT * FROM tblKhoahoc_Hocvien
    WHERE FK_iKhoahocID=@FK_iKhoahocID
GO
--sp get Khoahoc_Hocvien by FK_iHocvienID
CREATE PROC [dbo].[spKhoahoc_Hocvien_GetByFK_iHocvienID]
    @FK_iHocvienID bigint
AS
    SELECT * FROM tblKhoahoc_Hocvien
    WHERE FK_iHocvienID=@FK_iHocvienID
GO
--sp Kiểm tra tình trạng đăng ký khóa học của học viên
CREATE proc spDangkykhoahoc_GetByFK_iKhoahocIDAndFK_iHocvienID
@FK_iHocvienID bigint,
@FK_iKhoahocID bigint
as
begin
	select * FROM tblDangkykhoahoc
	WHERE FK_iKhoahocID = @FK_iKhoahocID
	AND FK_iHocvienID = @FK_iHocvienID
end
GO
--sp Kiểm tra tình trạng tham gia khóa học của học viên
CREATE proc spKhoahoc_Hocvien_GetByFK_iKhoahocIDAndFK_iHocvienID
@FK_iHocvienID bigint,
@FK_iKhoahocID bigint
as
begin
	select * FROM tblKhoahoc_Hocvien
	WHERE FK_iKhoahocID = @FK_iKhoahocID
	AND FK_iHocvienID = @FK_iHocvienID
end
GO
--sp lấy Khoahoc_Baihoc theo FK_iKhoahocID + FK_iBaihocID
CREATE proc sp_Khoahoc_Baihoc_GetByFK_iKhoahocIDAndFK_iBaihocID
	@FK_iKhoahocID Bigint,
	@FK_iBaihocID Bigint
as
begin
	select * FROM tblKhoahoc_Baihoc 
	WHERE FK_iKhoahocID = @FK_iKhoahocID
	AND FK_iBaihocID = @FK_iBaihocID
end